﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Extensions.Configuration;
using System.IO;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System.Data;

namespace Common
{
    public class MYSQLHelper
    {
        static MYSQLHelper()
        {
             var builder = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json");

            IConfigurationRoot config = builder.Build();
            ConnectionString = config["ConnectionStrings:CrmMySalConn"];
        }

        public static string ConnectionString = null;


        public static void  Query(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                //DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    connection.Close();
                    //command.Fill(ds, "ds");
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    connection.Close();
                    throw new Exception(ex.Message);
                }
                //return ds.Tables[0];
            }
        }

        public static DataSet Query_New(string SQLString, string connstr = "")
        {
            connstr = ConnectionString;
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    connection.Close();
                    command.Fill(ds, "ds");
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }


        public static int ExecuteUpMySql(string sql)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    conn.Open();
                    int res;
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    res = cmd.ExecuteNonQuery();
                    conn.Close();
                    return res; 
                }
            }
            catch (Exception e)
            {
                return -1;
            }

        }



        public static DataTable ExecQuery(string sql, string connstr) 
        {
			try
			{

				using (MySqlConnection connection = new MySqlConnection(connstr))
				{
					DataSet ds = new DataSet();

					connection.Open();
					MySqlDataAdapter command = new MySqlDataAdapter(sql, connection);
					connection.Close();
					command.Fill(ds, "ds");

					return ds.Tables[0];
				}
			}
			catch (MySql.Data.MySqlClient.MySqlException ex)
			{
				throw new Exception(ex.Message);
			}
		}

		public static int ExecNonQuery(string sql, string connstr,Dictionary<string,object> parameters)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connstr))
                {
                    conn.Open();
                    int res;
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
					foreach (KeyValuePair<string,object> item in parameters)
					{
                        cmd.Parameters.AddWithValue(item.Key, item.Value);
					}
                    res = cmd.ExecuteNonQuery();
                    conn.Close();
                    return res;
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

    }
}
